package com.persistence.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.model.LineaProduccion;
import com.model.Personal;
import com.persistence.dao.DAOException;
import com.persistence.dao.DAOManagerMain;
import com.persistence.dao.interfaces.DAOPersonal;

public class MySQLDAOPersonal implements DAOPersonal {
	
	private Personal personal = null;
	
	
	public MySQLDAOPersonal() {
	}

	public int insert(Personal personal) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection.prepareStatement("insert into Personal ("
					+ "Id_Personal, nombre, apellido, direccion, telefono, "
					+ "cuil, mail, id_Linea "
					+ ") " + "values (?, ?, ?, ?, ?, ?, ?, ?) ");

			stmt.setInt(1, personal.getId_Personal());
			stmt.setString(2, personal.getNombre());
			stmt.setString(3, personal.getApellido());
			stmt.setString(4, personal.getDireccion());
			stmt.setString(5, personal.getTelefono());
			stmt.setString(6, personal.getCuil());
			stmt.setString(7, personal.getMail());			
			stmt.setInt(8, personal.getId_Linea().getNroLinea());
			stmt.executeUpdate();
			
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);

		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public int update(Personal personal) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("update Personal " 
							+ "set nombre = ?, apellido = ?, direccion = ?, telefono = ?,"
							+ "cuil = ?, mail = ?, id_Lina = ?"
							+ " where id_Personal = ? ");

			stmt.setString(1, personal.getNombre());
         stmt.setString(2, personal.getApellido());
         stmt.setString(3, personal.getDireccion());
         stmt.setString(4, personal.getTelefono());
         stmt.setString(5, personal.getCuil());
         stmt.setString(6, personal.getMail());       
         stmt.setInt(7, personal.getId_Linea().getNroLinea());

			stmt.executeUpdate();
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public int eliminate(Personal personal) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("delete from Personal where id_Personal = ? ");
			stmt.setLong(1, personal.getId_Personal());

			stmt.executeUpdate();
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public ArrayList<Personal> select() throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		ArrayList<Personal> personals = new ArrayList<Personal>();

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection.prepareStatement("select P.*, L.* from"
			+ " personal P, lineaproduccion L "
         + "where P.Id_Linea=L.Id_Linea ");
			rs = stmt.executeQuery();

			while (rs.next()) {
				Personal personal = new Personal();
				personal.setId_Personal(rs.getInt("Id_Personal"));
				personal.setNombre(rs.getString("nombre"));
				personal.setApellido(rs.getString("apellido"));
				personal.setDireccion(rs.getString("direccion"));
				personal.setTelefono(rs.getString("telefono"));
				personal.setCuil(rs.getString("cuil"));
				personal.setMail(rs.getString("mail"));
				personal.setId_Linea(new LineaProduccion());
            personal.getId_Linea().setNroLinea(rs.getInt("Id_Linea"));
            
				personals.add(personal);		
			}

			return personals;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public Personal findById(int id) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		personal = new Personal();

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("select P.*, L.* from"
         + " personal P, lineaproduccion L "
         + "where P.Id_Linea=L.Id_Linea "   
					+		"an where Id_Personal = ?");
			stmt.setLong(1, id);
			rs = stmt.executeQuery();

			if (rs.next()) {
			   Personal personal = new Personal();
			   personal.setId_Personal(rs.getInt("Id_Personal"));
            personal.setNombre(rs.getString("nombre"));
            personal.setApellido(rs.getString("apellido"));
            personal.setDireccion(rs.getString("direccion"));
            personal.setTelefono(rs.getString("telefono"));
            personal.setCuil(rs.getString("cuil"));
            personal.setMail(rs.getString("mail"));
            personal.setId_Linea(new LineaProduccion());
            personal.getId_Linea().setNroLinea(rs.getInt("Id_Linea"));
			}

			return personal;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	@Override
	public boolean existInstance(Personal personal) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		boolean result = false;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("select * from Personal where UCASE(Nombre)");

			stmt.setString(1, personal.getNombre().toUpperCase());
//			stmt.setString(2, personal.getApellido().toUpperCase());
			rs = stmt.executeQuery();

			if (rs.next()) {
				result = true;
			}

			return result;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}
}
